﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataControl;
using System.Data;
using System.Text;
using System.Collections;

namespace ATW.MT.BUS
{
    public class bus_T_appuser
    {
        DataAccess da = null;
        public bus_T_appuser()
        {
            da = new DataAccess();
        }
        #region 方法
        /// <summary>
        /// app用户分页查询
        /// </summary>
        /// <param name="name">用户名称</param>
        /// <param name="type">用户类型(1.外部，2.内部，3.分供商，4.客户端，5.销售人员)</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable UserPagerQuery(string name, string type, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select u.[TAUID]
                              ,u.[tauType]
                              ,u.[tauLoginId]
                              ,u.[tauName]
                              ,u.[tauCountry]
                              ,u.[tauAdress]
                              ,u.[tauEmail]
                              ,u.[tauIsAuthorised]
                              ,u.[tauRowStatus]
                              ,u.[tauAdditional1]
                              ,u.[tauAdditional2]
                              ,u.[tauAdditional3]
                              ,u.[tauAdditional4]
                              ,u.[tauAdditional5]
                              ,u.[tauAdditional6]
                              ,u.[tauAdditional7]
                              ,u.[tauAdditional8]
                              ,u.[tauAdditional9]
                              ,u.[tauAdditional10],c.dcName,c.rate,isnull(u2.tauName,'无') belongto,u2.tauAdditional3 code,ct.dcuDesc from T_AppUser u
                        left join (select * from T_AppUser where tauType='5' )u2 on u.tauAdditional3=u2.tauAdditional1
                        left join D_Country ct on u.tauCountry=ct.dcuDesc and ct.RowStatus<>'2' 
                        left join D_Currency c on u.tauAdditional5=c.DCID where 1=1 ");
            if (!String.IsNullOrEmpty(type))
            {
                sql.Append(" and u.tautype ='" + type + "'");
            }
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append(" and u.tauName like '%" + name + "%'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "TAUID");
        }
        /// <summary>
        /// 用户明细查询
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public DataTable UserSingleQuery(string userid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select u.[TAUID]
                              ,u.[tauType]
                              ,u.[tauLoginId]
                              ,u.[tauName]
                              ,u.[tauCountry]
                              ,u.[tauAdress]
                              ,u.[tauEmail]
                              ,u.[tauIsAuthorised]
                              ,u.[tauRowStatus]
                              ,u.[tauAdditional1]
                              ,u.[tauAdditional2]
                              ,u.[tauAdditional3]
                              ,u.[tauAdditional4]
                              ,u.[tauAdditional5]
                              ,u.[tauAdditional6]
                              ,u.[tauAdditional7]
                              ,u.[tauAdditional8]
                              ,u.[tauAdditional9]
                              ,u.[tauAdditional10],c.dcName,c.rate, isnull(u2.tauName,'无') belongto,ct.dcuDesc from T_AppUser u
                        left join (select * from T_AppUser where tauType='5' )u2 on u.tauAdditional3=u2.tauAdditional1
                        left join D_Country ct on u.tauCountry=ct.DCUID 
                        left join D_Currency c on u.tauAdditional5=c.DCID where u.tauid='" + userid + "'");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 用户更新
        /// </summary>
        /// <param name="Rank">客户等级</param>
        /// <param name="rate">客户系数</param>
        /// <param name="currency">客户货币</param>
        /// <param name="belongTo">所属销售</param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int UserUpdate(string Rank, string rate, string currency, string belongTo, string userid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"update t_appuser set ");
            if (!String.IsNullOrEmpty(Rank))
            {
                sql.Append(" tauAdditional6='" + Rank + "',");
            }
            if (!String.IsNullOrEmpty(rate))
            {
                sql.Append(" tauAdditional4='" + rate + "',");
            }
            if (!String.IsNullOrEmpty(currency))
            {
                sql.Append(" tauAdditional5='" + currency + "',");
            }
            if (!String.IsNullOrEmpty(belongTo))
            {
                sql.Append(" tauAdditional3='" + belongTo + "',tauAdditional2='" + belongTo + "',");
            }
            sql.Append(" UpdatedDate= GETDATE() ");
            sql.Append("  where tauid='" + userid + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 查询币种
        /// </summary>
        /// <returns></returns>
        public DataTable CurrencyQuery()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select dcid,dcname from d_currency");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 销售人员分页查询
        /// </summary>
        /// <param name="name"></param>
        /// <param name="status">0正常 1删除</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable SellerPagerQuery(string name, string status, int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select tauName,
                                        tauid,
                                        tauEmail,
                                       tauLoginId,
                                       CreatedDate,
                                       tauRowStatus,
                                       tauAdditional1 codeSelf,
                                       s1.cnum
                                from T_AppUser au
                                left join (select COUNT(*) cnum,tauAdditional3 from T_AppUser where tautype='4' group by tauAdditional3) s1 
	                                on au.tauAdditional1=s1.tauAdditional3 where 1=1 and au.tautype='5'");
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append(" and tauName='%" + name + "%'");
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql.Append(" and tauRowStatus='" + status + "'");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "tauid", "CreatedDate desc");
        }
        /// <summary>
        /// 新增销售
        /// </summary>
        /// <param name="name">姓名</param>
        /// <param name="account">账号</param>
        /// <param name="pwd">密码</param>
        /// <param name="code">邀请码</param>
        /// <returns></returns>
        public int SellerAdd(string name, string account, string pwd, string code,string mail)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"insert into T_AppUser(tauType,tauLoginId,tauName,tauPsw,tauAdditional1,tauEmail) values('5','{0}','{1}','{2}','{3}','{4}')",
                account, name, pwd, code, mail);
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 销售是否存在
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool isSellerExist(string account)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select * from T_AppUser where tauLoginId='{0}'", account);
            DataTable dt = da.ExecuteDataTable(sql.ToString());
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 删除销售
        /// </summary>
        /// <param name="tauloginid">id</param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool SellerDel(string tauloginID)
        {
            ArrayList al = new ArrayList();
            al.Add("update T_appuser set taurowstatus='2' where tauloginID='" + tauloginID + "'");
            al.Add("update T_AppUser set tauAdditional3='' where tauAdditional3=(select * from T_AppUser where tauLoginId='" + tauloginID + "')");
            return da.ExecuteArrayList(al);
        }
        /// <summary>
        /// 恢复销售
        /// </summary>
        /// <param name="tauloginID"></param>
        /// <returns></returns>
        public int SellRecover(string tauloginID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update T_appuser set taurowstatus='0' where tauloginID='" + tauloginID + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 获取无所属的客户列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetUnlockedCustomer()
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select * from T_appuser where tautype='4' and taurowstatus<>'2' and isnull(tauAdditional3,'')=''");
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 将客户分配给销售
        /// </summary>
        /// <param name="tauid"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        public string AllocateCustomer(string tauid, string code)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update t_appuser set tauAdditional3='" + code + "' where tauloginid='" + tauid + "'");
            return sql.ToString();
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="al"></param>
        /// <returns></returns>
        public bool ExAl(ArrayList al)
        {
            return da.ExecuteArrayList(al);
        }
        #endregion
    }
}